﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using VisionarySystems.Library;
using System.Windows.Forms;
using VisionarySystems.TimeRegi.Model.TimePeriods;
using VisionarySystems.TimeRegi.Registry;

namespace VisionarySystems.TimeRegi.Database
{
    
    public class DataLayer 
	{
		#region Private Const
		private const string m_DatabaseName = "TimeRegi";
		private const string m_TableName1 = "RegDays";
		private const string m_TableName2 = "TimeSlots";
		private const string m_TableName3 = "SystemInfo";
		private const int CurrentVersionNo = 1; //10-04-2011 SLP
		#endregion

		#region private members

		private string m_DataSource = string.Empty;

		private static DatabaseUtils m_DatabaseUtils;

	    public void OnDeleteTimeSlot (int Idx)
	    {
		    DeleteTimeSlot(Idx);
	    }

	    //private RegistryLayer m_RegistryLayer;

		#endregion

		#region Public methods

		public static bool SaveTimeSlot(DateTime StartTime, DateTime StopTime, TimeSlotTypes TimeSlotType, bool TimeSlotSaved, out int NewIdx)
		{
			bool Result = false;
			NewIdx = 0;
			string SqlSaveToRegDays = @"
			INSERT INTO RegDays VALUES (@NewDay)";

			string SqlSaveToTimeSlots = @"
			INSERT INTO TimeSlots VALUES (@Start, @Stop, @RegDate, @TimeSlotType)";

			if (StartTime > StopTime)
				return false;
			if (TimeSlotSaved)
				return true;
			SqlTransaction SqlTrans = null;
			SqlConnection Conn = null;
			try
			{
				Conn = m_DatabaseUtils.GetConnection(m_DatabaseName);
				if (Conn.State != ConnectionState.Open)
					Conn.Open();
				SqlTrans = Conn.BeginTransaction();
				SqlCommand cmdRegDays = Conn.CreateCommand();

				if (!(m_DatabaseUtils.CheckRowExists(Conn, SqlTrans, "RegDays", "RegDate", StartTime)))
				{
					cmdRegDays.CommandText = SqlSaveToRegDays;
					cmdRegDays.Transaction = SqlTrans;
					cmdRegDays.Parameters.Add("@NewDay", SqlDbType.Date);
					cmdRegDays.Parameters["@NewDay"].Value = StartTime;
					cmdRegDays.ExecuteNonQuery();
				}

				SqlCommand cmdTimeSlot = Conn.CreateCommand();
				cmdTimeSlot.CommandText = SqlSaveToTimeSlots;
				cmdTimeSlot.Transaction = SqlTrans;
				cmdTimeSlot.Parameters.Add("@Start", SqlDbType.DateTime);
				cmdTimeSlot.Parameters["@Start"].Value = StartTime;
				cmdTimeSlot.Parameters.Add("@Stop", SqlDbType.DateTime);
				cmdTimeSlot.Parameters["@Stop"].Value = StopTime;
				cmdTimeSlot.Parameters.Add("@RegDate", SqlDbType.Date);
				cmdTimeSlot.Parameters["@RegDate"].Value = StartTime;
				cmdTimeSlot.Parameters.Add("@TimeSlotType", SqlDbType.Int);
				cmdTimeSlot.Parameters["@TimeSlotType"].Value = (int)TimeSlotType;
				cmdTimeSlot.ExecuteNonQuery();

				SqlTrans.Commit();
			}
			catch (System.Data.SqlClient.SqlException ex)
			{
				if (SqlTrans != null)
					SqlTrans.Rollback();
				MessageBox.Show("SQL fejl :" + ex.Message, "Error");
			}

			catch (System.Exception ex)
			{
				if (SqlTrans != null)
					SqlTrans.Rollback();
				MessageBox.Show("Systemfejl\n" + ex.Message, "Error");
			}
			finally
			{
				if (Conn.State == ConnectionState.Open)
					Conn.Close();
			}
			return Result;

		}
		
		public static int GetSecondsInPeriod(DateTime StartDate, DateTime StopDate, int TimeSlotType)
		{
			int? Result = 0;
			string SqlGetSeconds = @"select sum(datediff(second, convert(char(10), ([Stop] - Start), 120), ([Stop] - Start))) TotalSeconds
				from TimeSlots 
				Where RegDate >= @StartDate and RegDate <= @StopDate";
			/*Group By RegDate";*/

			SqlDataReader rdr = null;
			SqlConnection Conn = null;
			try
			{
				Conn = m_DatabaseUtils.GetConnection(m_DatabaseName);
				if (Conn.State != ConnectionState.Open)
					Conn.Open();
				SqlCommand cmdCheckTable = Conn.CreateCommand();
				cmdCheckTable.CommandText = SqlGetSeconds;
				cmdCheckTable.Parameters.Add("@StartDate", System.Data.SqlDbType.Date);
				cmdCheckTable.Parameters.Add("@StopDate", System.Data.SqlDbType.Date);
				cmdCheckTable.Parameters["@StartDate"].Value = StartDate;
				cmdCheckTable.Parameters["@StopDate"].Value = StopDate;
				rdr = cmdCheckTable.ExecuteReader();
				if (rdr.Read())
				{
					if (rdr["TotalSeconds"] == DBNull.Value)
						Result = 0;
					else
						Result = (int?)rdr["TotalSeconds"];
				}
				else
					Result = 0;

			}
			finally
			{
				rdr.Close();
				if (Conn.State == ConnectionState.Open)
					Conn.Close();
			}
			if (Result.HasValue)
				return Result.Value;
			else
				return 0;

		}

		public static void DeleteTimeSlot(int DeleteIdx)
		{
			SqlConnection Conn = null;
			try
			{
				Conn = m_DatabaseUtils.GetConnection(m_DatabaseName);
				DeleteTimeSlot(Conn, null, DeleteIdx);
			}
			finally
			{
				if (Conn.State == ConnectionState.Open)
					Conn.Close();
			}

		}

		public static List<TimeSlot> LoadAllTimeSlots()
		{
			SqlConnection Conn = m_DatabaseUtils.GetConnection(m_DatabaseName);
			List<TimeSlot> result = new List<TimeSlot>();
			SqlDataReader rdr = null;
			string SqlLoadTimeSlots = @"select * from TimeSlots order by start";

			try
			{
				if (Conn.State != ConnectionState.Open)
					Conn.Open();

				SqlCommand cmdTimeSlot = Conn.CreateCommand();
				cmdTimeSlot.CommandText = SqlLoadTimeSlots;
				rdr = cmdTimeSlot.ExecuteReader();


				while (rdr.Read())
				{
					int idx = Int32.Parse(rdr["Idx"].ToString());
					DateTime start = DateTime.Parse(rdr["Start"].ToString());
					DateTime stop = DateTime.Parse(rdr["Stop"].ToString());
					//string stype = rdr["TimeSlotType"].ToString();
					int type = (Int32.Parse(rdr["TimeSlotType"].ToString()));
					result.Add(new TimeSlot(idx, start, stop, (TimeSlotTypes)type));
				}
			}
			finally
			{
				if (rdr != null)
					rdr.Close();
			}
			return result;

		}

		public void CheckOrCreateDatabase()
		{
			m_DatabaseUtils = new DatabaseUtils(RegistryLayer.DataSource);
			if (!m_DatabaseUtils.CheckDatabaseExists(m_DatabaseName))
				m_DatabaseUtils.CreateDatabase(m_DatabaseName);
			SqlConnection Conn = null;
			try
			{
				Conn = m_DatabaseUtils.GetConnection(m_DatabaseName);
				if (!(m_DatabaseUtils.CheckTableExists(Conn, m_TableName1)))
					CreateTable(Conn, m_TableName1);
				if (!(m_DatabaseUtils.CheckTableExists(Conn, m_TableName2)))
					CreateTable(Conn, m_TableName2);
				//                if (!(m_DatabaseUtils.CheckTableExists(Conn, tableName3)))
				//                    CreateTable(Conn, tableName3);
				CheckDatabaseVersion();
			}
			finally
			{
				if (Conn.State == ConnectionState.Open)
					Conn.Close();
			}
		}
		
		#endregion

		#region Private Methods


		private static bool DeleteTimeSlot(SqlConnection Conn, SqlTransaction SqlTrans, int Idx)
		{
			bool Result = false;
			//string SqlSaveToRegDays = @"
			//INSERT INTO RegDays VALUES (@NewDay)";

			string SqlDeleteTimeSlot = @"
			DELETE FROM TimeSlots where Idx = @Idx";

			try
			{
				if (Conn.State != ConnectionState.Open)
					Conn.Open();
				//TODO: Clean up RegDays

				SqlCommand cmdTimeSlot = Conn.CreateCommand();
				cmdTimeSlot.CommandText = SqlDeleteTimeSlot;
				if (SqlTrans != null)
					cmdTimeSlot.Transaction = SqlTrans;
				cmdTimeSlot.Parameters.Add("@Idx", SqlDbType.Int);
				cmdTimeSlot.Parameters["@Idx"].Value = Idx;
				cmdTimeSlot.ExecuteNonQuery();

				if (SqlTrans != null)
					SqlTrans.Commit();
			}
			catch (System.Data.SqlClient.SqlException ex)
			{
				if (SqlTrans != null)
					SqlTrans.Rollback();
				MessageBox.Show("SQL fejl :" + ex.Message, "Error");
			}

			catch (System.Exception ex)
			{
				if (SqlTrans != null)
					SqlTrans.Rollback();
				MessageBox.Show("Systemfejl\n" + ex.Message, "Error");
			}

			finally
			{
				Result = true;
			}
			return Result;
		}

		private void CreateTable(SqlConnection Conn, string TableName)
		{
			string SqlCreateRegDaysTable = @"
			CREATE TABLE RegDays (
			RegDate Date not null Primary Key
			)";

			string SqlCreateTimeSlotTable = @"
			CREATE TABLE TimeSlots (
			Idx int IDENTITY(1000, 1) not null Primary Key
			, Start DateTime not null
			, Stop DateTime not null
			, RegDate Date not null 
				References RegDays
					On Update Cascade
						On delete Cascade)";

			string SqlCreateSystemInfoTable = @"
			CREATE TABLE SystemInfo (
			VersionNo int not null 
			)";
			try
			{
				if (Conn.State != ConnectionState.Open)
					Conn.Open();
				SqlCommand cmdCreateTable = Conn.CreateCommand();
				switch (TableName)
				{
					case m_TableName1: cmdCreateTable.CommandText = SqlCreateRegDaysTable; break;
					case m_TableName2: cmdCreateTable.CommandText = SqlCreateTimeSlotTable; break;
					case m_TableName3: cmdCreateTable.CommandText = SqlCreateSystemInfoTable; break;
					default: throw new Exception("Ukendt tabel : " + TableName);
				}

				cmdCreateTable.ExecuteNonQuery();
			}
			catch (SqlException se)
			{
				MessageBox.Show("fejl ved oprettelse af tabel " + TableName + ": " + se.Message);
			}
			finally
			{
				//                if (Conn.State == ConnectionState.Open)
				//                    Conn.Close();
			}
		}

		private static bool CheckDatabaseVersion()
		{
			TimeRegiDatabaseUpdater DBChecker = new TimeRegiDatabaseUpdater(RegistryLayer.DataSource, m_DatabaseName);
			int newVersionNo = DBChecker.CheckDatabaseVersion(CurrentVersionNo);
			return (newVersionNo == CurrentVersionNo);
		}

		#endregion
	}
}
